package com.sjax.myapp.service;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.apache.log4j.Logger;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.stereotype.Service;

import com.sjax.myapp.domain.User;
import com.sjax.myapp.json.Criterion;
import com.sjax.myapp.utils.JqGridUtils;
import com.sjax.myapp.utils.PaginationUtils;

@Service("userService")
public class UserService {

	private static Logger logger = Logger.getLogger("service");

	private SimpleJdbcTemplate jdbcTemplate;

	@Resource(name = "dataSource")
	public void setDataSource(DataSource dataSource) {
		this.jdbcTemplate = new SimpleJdbcTemplate(dataSource);
	}

	public int add(User user) {

		logger.debug("Add New User");

		String sql = "INSERT INTO user (id,firstName,lastName)VALUE(:id,:firstName,:lastName)";

		// 生成一个UUID
		String uuid = UUID.randomUUID().toString();

		Map<String, Object> parameters = new HashMap<String, Object>();
		parameters.put("id", uuid);
		parameters.put("firstName", user.getFirstName());
		parameters.put("lastName", user.getLastName());
		return jdbcTemplate.update(sql, parameters);
	}

	public int delete(User user) {

		logger.debug("根据ID:{" + user.getId() + "}Delete User");

		return jdbcTemplate.update("DELETE FROM user WHERE id  = ?",
				new Object[] { user.getId() });
	}

	public int[] deleteMore(List<User> userList) {

		logger.debug("根据ID批量删除{" + userList.size() + "}条数据");

		String sql = "DELETE FROM user WHERE id  = ?";

		List<Object[]> batch = new ArrayList<Object[]>();

		for (User user : userList) {
			Object[] values = new Object[] { user.getId() };
			batch.add(values);
		}

		return jdbcTemplate.batchUpdate(sql, batch);

	}

	public int edit(User user) {

		logger.debug("根据ID:{" + user.getId() + "}Edit User");

		String sql = "UPDATE user SET firstName = :firstName,lastName = :lastName WHERE id = :id";

		Map<String, Object> parameters = new HashMap<String, Object>();
		parameters.put("id", user.getId());
		parameters.put("firstName", user.getFirstName());
		parameters.put("lastName", user.getLastName());

		return jdbcTemplate.update(sql, parameters);
	}

	public User get(String id) {

		logger.debug("根据ID: {" + id + "}获得User");

		String sql = "select id,firstName,lastName from user where id = ?";

		RowMapper<User> mapper = new RowMapper<User>() {

			public User mapRow(ResultSet rs, int rowNum) throws SQLException {
				User user = new User();
				user.setId(rs.getString("id"));
				user.setFirstName(rs.getString("firstName"));
				user.setLastName(rs.getString("lastName"));
				return user;
			}
		};

		return jdbcTemplate.queryForObject(sql, mapper, new Object[] { id });
	}

	public long getAllCount() {

		// logger.debug("查询所有User的个数");

		return jdbcTemplate.queryForLong("select count(id) from user");
	}

	/**
	 * 查询所有的User,注:下面4个参数是jqgrid自带的参数.只需在后台接收.
	 * 
	 * @param page
	 *            当前页
	 * @param rows
	 *            总行数
	 * @param sidx
	 *            排序字段
	 * @param sord
	 *            排序方式
	 * @return
	 */
	public List<User> getAll(int page, int rows, String sidx, String sord) {

		// logger.debug("查询所有的User");

		String sql = "select id, firstName ,lastName from user order by "
				+ sidx + " " + sord + "";

		RowMapper<User> mapper = new RowMapper<User>() {

			public User mapRow(ResultSet rs, int rowNum) throws SQLException {
				User user = new User();
				user.setId(rs.getString("id"));
				user.setFirstName(rs.getString("firstName"));
				user.setLastName(rs.getString("lastName"));
				return user;
			}
		};

		// 对sql进行分页处理
		sql = PaginationUtils.paginationSQLForMySQL(sql, page, rows);

		return jdbcTemplate.query(sql, mapper);
	}

	/**
	 * 根据条件进行查询
	 * 
	 * @param page
	 * @param rows
	 * @param sidex
	 * @param sord
	 * @return
	 */
	public List<User> getUserByParameter(int page, int rows, String sidx,
			String sord, String filters) {

		logger.debug("根据条件进行查询");
		String sql, sql1, sql2, sql3;

		List<Criterion> criteria = new ArrayList<Criterion>();
		criteria.addAll(JqGridUtils.generateSearchCriteriaFromFilters(filters));

		sql1 = "select id, firstName ,lastName from user";

		Object[] parameters = new Object[criteria.size()];
		int index = 0;

		sql2 = JqGridUtils.converToSql(criteria,"user");

		for (int i = 0; i < criteria.size(); i++) {
			parameters[index] = criteria.get(i).getValue();
			index++;
		}
		Object[] paramEnd = new Object[index];

		sql3 = " order by " + sidx + " " + sord + "";

		System.arraycopy(parameters, 0, paramEnd, 0, index);

		sql = sql1 + sql2 + sql3;

		sql = PaginationUtils.paginationSQLForMySQL(sql, page, rows);

		RowMapper<User> mapper = new RowMapper<User>() {

			public User mapRow(ResultSet rs, int rowNum) throws SQLException {
				User user = new User();
				user.setId(rs.getString("id"));
				user.setFirstName(rs.getString("firstName"));
				user.setLastName(rs.getString("lastName"));
				return user;
			}
		};

		return jdbcTemplate.query(sql, mapper, paramEnd);

	}

	public long getUserByParameterCount(String filters) {

		String sql, sql1, sql2;

		sql1 = "select COUNT(id) from user ";

		List<Criterion> criteria = new ArrayList<Criterion>();

		criteria.addAll(JqGridUtils.generateSearchCriteriaFromFilters(filters));

		sql2 = JqGridUtils.converToSql(criteria,"user");

		sql = sql1 + sql2;

		Object[] parameters = new Object[criteria.size()];

		int index = 0;

		for (int i = 0; i < criteria.size(); i++) {
			parameters[index] = criteria.get(i).getValue();
			index++;
		}

		Object[] paramEnd = new Object[index];

		System.arraycopy(parameters, 0, paramEnd, 0, index);

		return jdbcTemplate.queryForLong(sql, paramEnd);
	}

}
